package com.webwork.common.dao;

import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;

import org.apache.commons.lang3.StringUtils;

/**
 * 数据库操作SQL
 *
 */
public final class DBSimpleSQL {

	private final static String END_WITH = ", ";

	/**
	 * select @param show1, @param show2 from @param table where @param limit1
	 * and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @param show
	 * @return
	 */
	public static String select(String table, ColumnLimit limit, String... show) {
		StringBuffer sql = new StringBuffer("SELECT ");
		for (String s : show) {
			sql.append(s.concat(END_WITH));
		}
		sql.setLength(sql.length() - END_WITH.length());
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String selectWithIndex(String table, ColumnLimit limit, String... show) {
		StringBuffer sql = new StringBuffer("SELECT ");
		for (String s : show) {
			sql.append(s.concat(END_WITH));
		}
		sql.setLength(sql.length() - END_WITH.length());
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * select distinct(@param distinct) from @param table where @param limit1
	 * and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @param distinct
	 * @return
	 */
	public static String selectDistince(String table, ColumnLimit limit, String... distinct) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("DISTINCT ");
		for (int i = 0; i < distinct.length - 1; i++) {
			sql.append(distinct[i].concat(" , "));
		}
		sql.append(distinct[distinct.length - 1]);
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String selectDistinceWithIndex(String table, ColumnLimit limit, String... distinct) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("DISTINCT ");
		for (int i = 0; i < distinct.length - 1; i++) {
			sql.append(distinct[i].concat(" , "));
		}
		sql.append(distinct[distinct.length - 1]);
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * 把所有的数据去重输出（多个判断字段）
	 * 
	 * @param table
	 * @param distinct
	 * @return
	 */
	public static String selectDistince(String table, String... distinct) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("DISTINCT ");
		for (int i = 0; i < distinct.length - 1; i++) {
			sql.append(distinct[i].concat(" , "));
		}
		sql.append(distinct[distinct.length - 1]);
		sql.append(" FROM ");
		sql.append(table.trim());
		return sql.toString();
	}

	/**
	 * ??
	 */
	public static String selectDistince(String table, ColumnLimit limit, String distinct1, String distinct2) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("DISTINCT(");
		sql.append(distinct1);
		sql.append("),");
		sql.append(distinct2);
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String selectDistinceWithIndex(String table, ColumnLimit limit, String distinct1, String distinct2) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("DISTINCT(");
		sql.append(distinct1);
		sql.append("),");
		sql.append(distinct2);
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * 别名<br>
	 * select map.value as map.key from @param table where @param limit1
	 * and @param limit2<br>
	 * limit可别名也可列名
	 * 
	 * @param table
	 * @param limit
	 * @param map
	 * @return
	 */
	public static String selectAilas(String table, ColumnLimit limit, Map<String, String> map) {
		StringBuffer sql = new StringBuffer("SELECT ");
		for (Object key : map.keySet()) {
			sql.append(map.get(key));
			sql.append(" as ");
			sql.append(key);
			sql.append(END_WITH);
		}
		sql.setLength(sql.length() - END_WITH.length());
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String selectAilasWithIndex(String table, ColumnLimit limit, Map<String, String> map) {
		StringBuffer sql = new StringBuffer("SELECT ");
		for (Object key : map.keySet()) {
			sql.append(map.get(key));
			sql.append(" AS ");
			sql.append(key);
			sql.append(END_WITH);
		}
		sql.setLength(sql.length() - END_WITH.length());
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * select * from @param table where @param limit1 and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String selectAllWithIndex(String table, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * select * from @param table where @param limit1 and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @param colmn
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit, String... colmn) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		sql.append(" GROUP BY ");
		for (String c : colmn) {
			sql.append(c.concat(END_WITH));
		}
		sql.setLength(sql.length() - END_WITH.length());
		return sql.toString();
	}

	public static String selectAllWithIndex(String table, ColumnLimit limit, String... colmn) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		sql.append(" GROUP BY ");
		for (String c : colmn) {
			sql.append(c.concat(END_WITH));
		}
		sql.setLength(sql.length() - END_WITH.length());
		return sql.toString();
	}

	public static String selectAilasGroup(String table, ColumnLimit limit, Map<String, String> map, int currPage,
			int pageSize, String... colmn) {
		StringBuffer sql = new StringBuffer("SELECT ");
		for (Object key : map.keySet()) {
			sql.append(map.get(key));
			sql.append(" as ");
			sql.append(key);
			sql.append(END_WITH);
		}
		sql.setLength(sql.length() - END_WITH.length());
		sql.append(" FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));

		sql.append(" GROUP BY ");
		for (String c : colmn) {
			sql.append(c.concat(END_WITH));
		}
		sql.setLength(sql.length() - END_WITH.length());

		if (pageSize > 0 && currPage > 0) {
			sql.append(" LIMIT ");
			sql.append(String.valueOf((currPage - 1) * pageSize));
			sql.append(", ");
			sql.append(String.valueOf(pageSize));
		}
		return sql.toString();
	}

	/**
	 * 按条件搜索表的所有数据 表名 table 列名 limit 页数 currPage 行数 pageSize
	 * 
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit, int currPage, int pageSize) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));

		if (pageSize > 0 && currPage > 0) {
			sql.append(" LIMIT ");
			sql.append(String.valueOf((currPage - 1) * pageSize));
			sql.append(", ");
			sql.append(String.valueOf(pageSize));
		}
		return sql.toString();
	}

	public static String selectAllWithIndex(String table, ColumnLimit limit, int currPage, int pageSize) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));

		if (pageSize > 0 && currPage > 0) {
			sql.append(" LIMIT ");
			sql.append(String.valueOf((currPage - 1) * pageSize));
			sql.append(", ");
			sql.append(String.valueOf(pageSize));
		}
		return sql.toString();
	}

	/**
	 * 按条件搜索表的所有数据并分组 表名 table 列名 limit 页数 currPage 行数 pageSize 唯一 colmn
	 * 
	 * @return
	 */
	public static String selectAll(String table, ColumnLimit limit, int currPage, int pageSize, String... colmn) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		sql.append(" GROUP BY ");
		for (String c : colmn) {
			sql.append(c.concat(END_WITH));
		}
		sql.setLength(sql.length() - END_WITH.length());
		if (pageSize > 0 && currPage > 0) {
			sql.append(" LIMIT ");
			sql.append(String.valueOf((currPage - 1) * pageSize));
			sql.append(", ");
			sql.append(String.valueOf(pageSize));
		}
		return sql.toString();
	}

	public static String selectAllWithIndex(String table, ColumnLimit limit, int currPage, int pageSize,
			String... colmn) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		sql.append(" GROUP BY ");
		for (String c : colmn) {
			sql.append(c.concat(END_WITH));
		}
		sql.setLength(sql.length() - END_WITH.length());
		if (pageSize > 0 && currPage > 0) {
			sql.append(" LIMIT ");
			sql.append(String.valueOf((currPage - 1) * pageSize));
			sql.append(", ");
			sql.append(String.valueOf(pageSize));
		}
		return sql.toString();
	}

	/**
	 * select count(*) from @param table where @param limit1 and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @return
	 */
	public static String count(String table, ColumnLimit limit) {
		return DBSimpleSQL.count(table, limit, "");
	}

	public static String countWithIndex(String table, ColumnLimit limit) {
		return DBSimpleSQL.countWithIndex(table, limit, "");
	}

	/**
	 * select count(distinct(*)) from @param table where @param limit1
	 * and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @param colmn
	 * @return
	 */
	public static String count(String table, ColumnLimit limit, String... colmn) {
		StringBuffer sql;
		StringBuffer str = new StringBuffer();
		for (int i = 0; i < colmn.length; i++) {
			if (colmn.length > 0 && i < colmn.length - 1) {
				str.append(colmn[i].concat(","));
			} else if (colmn.length > 0 && i == colmn.length - 1) {
				str.append(colmn[i]);
			}
		}
		if (StringUtils.isEmpty(str.toString())) {
			sql = new StringBuffer("SELECT COUNT(*) FROM "); // 计算结果的数目
		} else {
			sql = new StringBuffer("SELECT COUNT(DISTINCT ");
			sql.append(str);
			sql.append(") FROM "); // 计算非重复结果的数目
		}
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String countWithIndex(String table, ColumnLimit limit, String... colmn) {
		StringBuffer sql;
		StringBuffer str = new StringBuffer();
		for (int i = 0; i < colmn.length; i++) {
			if (colmn.length > 0 && i < colmn.length - 1) {
				str.append(colmn[i].concat(","));
			} else if (colmn.length > 0 && i == colmn.length - 1) {
				str.append(colmn[i]);
			}
		}
		if (StringUtils.isEmpty(str.toString())) {
			sql = new StringBuffer("SELECT COUNT(*) FROM "); // 计算结果的数目
		} else {
			sql = new StringBuffer("SELECT COUNT(DISTINCT ");
			sql.append(str);
			sql.append(") FROM "); // 计算非重复结果的数目
		}
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * insert into @param table values (?, ?, ...)
	 * 
	 * @param table
	 * @param columnSize
	 * @return
	 */
	public static String insert(String table, int columnSize) {
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(table.trim());
		sql.append(" VALUES (");
		for (int i = 0; i < columnSize - 1; i++) {
			sql.append("?, ");
		}
		sql.append("?)");
		return sql.toString();
	}

	/**
	 * delete from @param table where @param limit1 and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @return
	 */
	public static String del(String table, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("DELETE FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String delWithIndex(String table, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("DELETE FROM ");
		sql.append(table.trim());
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * 按条件更新表的所需列
	 * 
	 * @param table
	 * @param dbColumns
	 * @param limit
	 * @return
	 */
	public static String update(String table, LinkedHashMap<String, String> dbColumns, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(table.trim());
		sql.append(" SET ");
		Iterator<Entry<String, String>> lit = dbColumns.entrySet().iterator();
		int i = 0;
		while (lit.hasNext()) {
			Map.Entry<String, String> colmn = (Map.Entry<String, String>) lit.next();
			sql.append(colmn.getValue().concat(" = ?"));
			if (i != dbColumns.size() - 1) {
				sql.append(END_WITH);
			}
			i++;
		}
		sql.append(createWhere(limit));
		return sql.toString();
	}

	public static String updateWithIndex(String table, LinkedHashMap<String, String> dbColumns, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(table.trim());
		sql.append(" SET ");
		Iterator<Entry<String, String>> lit = dbColumns.entrySet().iterator();
		int i = 0;
		while (lit.hasNext()) {
			Map.Entry<String, String> colmn = (Map.Entry<String, String>) lit.next();
			sql.append(colmn.getValue().concat(" = ?"));
			if (i != dbColumns.size() - 1) {
				sql.append(END_WITH);
			}
			i++;
		}
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * replace 插入数据 (遇到主键重复直接覆盖)
	 * 
	 * @param table
	 * @param columnSize
	 * @return
	 */
	public static String replace(String table, int columnSize) {
		StringBuffer sql = new StringBuffer("REPLACE INTO ");
		sql.append(table.trim());
		sql.append(" VALUES (");
		for (int i = 0; i < columnSize - 1; i++) {
			sql.append("?, ");
		}
		sql.append("?)");
		return sql.toString();
	}


	/**
	 * ALL关联查询语句
	 * 
	 * @param AbridgeTable
	 *            (table简称)
	 * @param sqlFrom
	 *            嵌套select语句
	 * @param limit
	 *            筛选条件
	 */
	public static String all_Union_Query(String abridgeTable, String sqlFrom, ColumnLimit limit) {
		StringBuffer sql = new StringBuffer("SELECT * FROM( ");
		sql.append(sqlFrom);
		sql.append(" ) ");
		sql.append(abridgeTable);
		sql.append(createWhere(limit));
		return sql.toString();
	}

	/**
	 * 创建条件判断语句
	 * 
	 * @param limit
	 * @return
	 */
	public static String createWhere(ColumnLimit limit) {
		if (limit.isEmpty()) {
			return " WHERE 1 = 1";
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" WHERE ");
		String endWith = limit.getBetweenColumnStr();
		for (String item : limit.getColumns()) {
			sql.append(item);
			if (limit.get(item) || StringUtils.isEmpty(item))
				sql.append("?");
			sql.append(endWith);
		}
		for (ColumnLimit cl : limit.getColumnObjs()) {
			String innerEndWith = cl.getBetweenColumnStr();
			sql.append("(");
			for (String item : cl.getColumns()) {
				sql.append(item);
				if (cl.get(item) || StringUtils.isEmpty(item))
					sql.append("?");
				sql.append(innerEndWith);
			}
			sql.setLength(sql.length() - innerEndWith.length());
			sql.append(")");
			sql.append(endWith);
		}
		sql.setLength(sql.length() - endWith.length());
		return sql.toString();
	}

	/**
	 * 添加问号如: "?, ?, ?"
	 * 
	 * @param length
	 * @return
	 */
	public static String addQuan(int length) {
		StringBuffer ret = new StringBuffer();
		for (int i = 0; i < length; i++) {
			ret.append("?".concat(END_WITH));
		}
		if (ret.length() > END_WITH.length()) {
			ret.setLength(ret.length() - END_WITH.length());
		}
		return ret.toString();
	}
	
	/**
	 * 一般查询
	 * @param table
	 * @param condition
	 * @return
	 */
	public static String select(String table, SqlCondition condition) {
		StringBuffer sql = new StringBuffer("SELECT * FROM ");
		sql.append(table.trim()).append(" WHERE 1=1 ");
		if (Objects.nonNull(condition.getConditionList())) {
			for (String s : condition.getConditionList()) {
				sql.append(s);
			}
		}
		return sql.toString();
		
	}
	
	/**
	 * 展示指定列
	 * @param table
	 * @param condition
	 * @param showCol
	 * @return
	 */
	public static String select(String table, SqlCondition condition, String... showCol) {
		return select(table, condition, 0, 0, showCol);
	}

	/**
	 * 分页 展示指定列, 有order by
	 * @param table
	 * @param condition
	 * @param groupByCol
	 * @param pageSize
	 * @param currPage
	 * @param showCol
	 * @return
	 */
	public static String select(String table, SqlCondition condition, String[] groupByCol, int pageSize, int currPage, String[] showCol, String[] orderBy) {
		StringBuffer sql = new StringBuffer("SELECT ");
		if (Objects.nonNull(showCol) && showCol.length > 0) {
			for (String show : showCol) {
				sql.append(show).append(",");
			}
			sql.deleteCharAt(sql.length() - 1);
		} else {
			sql.append(" * ");
		}
		sql.append(" FROM");
		// TABLE
		sql.append(" ").append(table.trim());
		// WHERE
		sql.append(" WHERE 1=1 ");
		if (Objects.nonNull(condition.getConditionList())) {
			for (String s : condition.getConditionList()) {
				sql.append(s);
			}
		}
		// GROUP BY
		if (Objects.nonNull(groupByCol) && groupByCol.length > 0) {
			sql.append(" GROUP BY ");
			for (String col : groupByCol) {
				sql.append(col).append(",");
			}
			sql.deleteCharAt(sql.length() - 1);
		}
		// ORDER BY
		if (Objects.nonNull(orderBy) && orderBy.length > 0) {
			sql.append(" ORDER BY ");
			for (String col : orderBy) {
				sql.append(col).append(",");
			}
			sql.deleteCharAt(sql.length() - 1);
		}
		// LIMIT
		if (pageSize > 0 && currPage > 0) {
			sql.append(" LIMIT ");
			sql.append(String.valueOf((currPage - 1) * pageSize));
			sql.append(", ");
			sql.append(String.valueOf(pageSize));
		}
		return sql.toString();
	}
	
	/**
	 * 分页 展示指定列, 无order by
	 * @param table
	 * @param condition
	 * @param pageSize
	 * @param currPage
	 * @param showCol
	 * @return
	 */
	public static String select(String table, SqlCondition condition, int pageSize, int currPage, String[] showCol) {
		return select(table, condition, null, pageSize, currPage, showCol, null);
	}
	
	/**
	 * 分页 展示指定列, 有order by
	 * @param table
	 * @param condition
	 * @param pageSize
	 * @param currPage
	 * @param showCol
	 * @return
	 */
	public static String select(String table, SqlCondition condition, int pageSize, int currPage, String[] showCol, String[] orderBy) {
		return select(table, condition, null, pageSize, currPage, showCol, orderBy);
	}
	
	/**
	 * 分页 展示所有列
	 * @param table
	 * @param condition
	 * @param pageSize
	 * @param currPage
	 * @return
	 */
	public static String select(String table, SqlCondition condition, int pageSize, int currPage) {
		return select(table, condition, null, pageSize, currPage, null, null);
	}
	
	/**
	 * count计数
	 * @param table
	 * @param condition
	 * @param pageSize
	 * @param currPage
	 * @param showCol
	 * @return
	 */
	public static String count(String table, SqlCondition condition) {
		return count(table, condition, null);
	}
	
	/**
	 * delete from @param table where @param limit1 and @param limit2
	 * 
	 * @param table
	 * @param limit
	 * @return
	 */
	public static String delete(String table, SqlCondition condition) {
		StringBuffer sql = new StringBuffer("DELETE FROM ");
		sql.append(table.trim()).append(" WHERE 1=1 ");
		if (Objects.nonNull(condition.getConditionList())) {
			for (String s : condition.getConditionList()) {
				sql.append(s);
			}
		}
		return sql.toString();
	}

	/**
	 * count计数 group by
	 * @param table
	 * @param condition
	 * @param groupByCol
	 * @return
	 */
	public static String count(String table, SqlCondition condition, String[] groupByCol) {
		StringBuffer sql = new StringBuffer("SELECT");
		sql.append(" COUNT(*) ");
		sql.append(" FROM ");
		sql.append(table.trim()).append(" WHERE 1=1 ");
		if (Objects.nonNull(condition.getConditionList())) {
			for (String s : condition.getConditionList()) {
				sql.append(s);
			}
		}
		// GROUP BY
		if (Objects.nonNull(groupByCol) && groupByCol.length > 0) {
			sql.append(" GROUP BY ");
			for (String col : groupByCol) {
				sql.append(col).append(",");
			}
			sql.deleteCharAt(sql.length() - 1);
		}
		return sql.toString();
	}
	
	/**
	 * on Duplicate Key Update
	 * @param table
	 * @param dbColumns
	 * @param row
	 * @return
	 */
	public String onDuplicateKeyUpdate(String table, LinkedHashMap<String, String> dbColumns, int row) {
		// value
		StringBuffer value = new StringBuffer();
		for (int i = 0; i < dbColumns.size(); i++) {
			if (i == 0) {
				value.append("(?, ");
			}
			if (i > 0 && i < dbColumns.size() - 1) {
				value.append("?, ");
			}
			if (i == dbColumns.size() - 1) {
				value.append("?)");
			}
		}
		// condition
		StringBuffer condition = new StringBuffer();
		condition.append(" VALUES ");
		for (int i = 0; i < row; i++) {
			if (i != row - 1) {
				condition.append(value.toString() + ", ");
			} else {
				condition.append(value.toString());
			}
		}
		// limit
		StringBuffer limit = new StringBuffer();
		limit.append(" ON DUPLICATE KEY UPDATE ");
		int n = 0;
		for (String dbColumn: dbColumns.keySet()) {
			if (n < dbColumns.size() - 1) {
				limit.append(dbColumns.get(dbColumn) + " = VALUES(" 
						+ dbColumns.get(dbColumn) + "), ");
			} else {
				limit.append(dbColumns.get(dbColumn) + " = VALUES(" 
						+ dbColumns.get(dbColumn) + ")");
			}
			n++;
		}
		
		StringBuffer sql = new StringBuffer("INSERT INTO " + table.trim());
		sql.append(condition.toString());
		sql.append(limit.toString());
		return sql.toString();
	}

}
